signon ;		
rsubmit;
libname bankrup ''; 
libname prwora ''; 
libname crime	'';
%let prwora =  ;
options compress=YES;

* MERGE PARENT SSNs INTO MAIN PRWORA FILE, THEN MERGE IN PARENT NAMES;

* Prep parent-child link;
data prwora.prwora_age18_allpar_hunpan;
	set prwora.prwora_allpar (keep=hun pan mft birth_jd);
	if mft='XS' then delete;
	if pan='*00000000' or pan='000000000' then delete;
	if hun='*00000000' or hun='000000000' then delete;
run;

proc sort data=prwora.prwora_age18_allpar_hunpan out=prwora.prwora_age18_allpar_hunpan nodupkey;
	by hun pan;
run;

* Merge parent SSNs into PRWORA files;
proc sort data=prwora.prwora_age18_small out=prwora.prwora_age18_small;
	by hun;
run;

data crime.prwora_age18_crimepar;

	merge 
		prwora.prwora_age18_small
			(in=from_prworax
			keep=hun dob first_pay_dte n_diag1cat n_firstdiag toa n_male dibmdr
				n_firstage pdscc age18_final_con adultcdr_term rcd_est)

		prwora.prwora_age18_allpar_hunpan
			(in=from_parlinkx keep=hun pan birth_jd mft
			 rename=(birth_jd=ssr_pardob mft=ssr_parmft))
		;

		by hun;
		if from_prworax=1;
		from_parlink=from_parlinkx;
		from_prwora=from_prworax;
	run;

* Some kids don't have parents so not all PRWORA records will match ;
proc tabulate data=crime.prwora_age18_crimepar;
	class from_prwora from_parlink;
	table from_prwora, from_parlink;
run;

* Merge in parent names;
proc sort data=crime.prwora_age18_crimepar out=crime.prwora_age18_crimepar;
	by pan;
run;

data crime.prwora_age18_crimepar;

	merge 
		crime.prwora_age18_crimepar
			(in=from_prwora2x drop=from_prwora from_parlink)

		bankrup.bankrup_ssrformerge 
			(in=from_namex
			rename=(ssn=pan lastn=par_lastn firstn=par_firstn middlen=par_middlen))
		;

	by pan;
	if from_prwora2x=1;
	from_prwora2=from_prwora2x;
	from_name=from_namex;
run;

* Most parents should match to a name, but remember that not all kids have parents ;
proc tabulate data=crime.prwora_age18_crimepar ;
	class from_prwora2 from_name;
	table from_prwora2, from_name;
run;

data crime.prwora_age18_crimepar;
	set crime.prwora_age18_crimepar (drop=from_prwora2 from_name);
run;

*--------------------*;
* Export Stata files *;
*--------------------*;
%let output =  ;
%macro stataexport(dsn);
    proc export data=crime.&dsn
        file="&output\&dsn..dta"
        dbms=DTA REPLACE;
    run;
%mend stataexport;

%stataexport(prwora_age18_crimepar);

endrsubmit;
signoff ;
